This invention relates generally to updating data in databases, and more particularly to positioned updates in distributed shared-nothing data stores.
A database cursor is a control structure that enables traversal over the records in a database for subsequent processing, such as retrieval, updating or removal, and is analogous to an iterator. Cursors are used to process individual rows returned by database system queries, and enable the rows of a result set to be processed sequentially.
In SQL procedures, an updatable cursor may define a result set, such as a set of data rows. Updatable cursors are a convenient and efficient procedure for performing complex logic on a row-by-row basis or returning the data set directly to a user that called the procedure. A user or client application can declare a SQL updatable cursor using the standard DECLARE CURSOR syntax to scroll the data set, and update a row of data positioned at the current location of the cursor using the WHERE CURRENT OF and UPDATE syntax. In order to support this functionality, the database management system (RDBMS) must be able to determine which row is currently positioned under the cursor. This is usually not a problem when all the data is in a single database that supports updatable cursors and is hosted by a single server. However, in a large distributed shared-nothing data store in which data is distributed across multiple disparate segment nodes, a current row of result data under a cursor may similarly be spread across multiple segment nodes and be stored in different physical locations on the segment nodes. When a user issues a command to update the current row of a result set under a cursor, the drivers on the segment nodes may have no idea on which row the cursor is actually positioned. This is because the WHERE statement of the updatable SQL cursor is imprecise. Thus, data in many different rows on the segments may be undesirably updated. Accordingly, updatable cursors have generally been unavailable for distributed shared-nothing data stores.
It is desirable to provide updatable cursors in a distributed shared-nothing data store and that address the foregoing and other problems of updatable cursors and updating data in distributed data stores, and it is to these ends that the present invention is directed.